﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessInstockGroup
// 文件功能描述：食材入库数据访问层
//
// 
// 创建标识：2012-01-14 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessInstockGroup
    {

        private string ConnectionString = string.Empty;


        private sqldb db = null;


        /// <summary>
        /// 构造函数
        /// </summary>
        public DataAccessInstockGroup()
        {
            ConnectionString = GetConntionString.conntionstring();
            db = new sqldb();
        }


        /// <summary>
        /// 保存食材入库
        /// </summary>
        /// <param name="igpara"></param>
        /// <returns></returns>
        public int SaveInstockGroup(object[] igpara)
        {
            sqldb db = new sqldb();
            return db.ExecuteNonQuery(GetConntionString.conntionstring(), "Proc_InstockGroup", igpara);
        }

        /// <summary>
        /// 获取一条食材入库信息
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns>食材入库信息</returns>
        public DataSet GetInstockGroup(int ID)
        {
            StringBuilder str = new StringBuilder();
            str.Append("select ID,Ord_No,Supplier_ID,Operator_ID,GoodsType,Remark,RecordDateTime "
                      +"from Con_Instock_Group where ID = @ID");
            SqlParameter[] para = new SqlParameter[]{
                new SqlParameter("@ID",ID)
            };
            return db.ExecuteDataset(ConnectionString, CommandType.Text, str.ToString(), para);
        }


        /// <summary>
        /// 获取食材入库信息
        /// </summary>
        /// <param name="pagesize">页大小</param>
        /// <param name="startindex">起始页</param>
        /// <param name="dict">查询条件</param>
        /// <returns>食材入库信息</returns>
        public DataSet GetInstockGroup(int pagesize, int startindex,
            string condition)
        {
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            StringBuilder str = new StringBuilder();
            str.Append("select ID,Ord_No,Supplier_ID,Operator_ID,GoodsType,Remark,RecordDateTime from (");
            str.Append("select ROW_NUMBER() OVER(ORDER BY ID DESC) rownumber,* ");
            str.Append("from Con_Instock_Group where 1=1 ");
            str.Append(condition);
            str.Append(") a ");
            str.Append("where a.rownumber > ");
            str.Append(startrow);
            str.Append(" and a.rownumber < ");
            str.Append(endrow);

            DataSet ds = db.ExecuteDataset(ConnectionString, CommandType.Text, str.ToString());
            return ds;

        }


        /// <summary>
        /// 删除食材入库
        /// </summary>
        /// <param name="ID">主键</param>
        /// <returns></returns>
        public int DeleteInstockGroup(string OrdNo)
        {
            int rowcount = 0;
            using (SqlConnection sqlconn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqlconn.Open();
                SqlTransaction sqltran = sqlconn.BeginTransaction();
                try
                {
                    sqldb db = new sqldb();
                    StringBuilder str = new StringBuilder();
                    str.Append("delete from Con_Instock_Group where Ord_No = " + OrdNo);
                    rowcount = rowcount + db.ExecuteNonQuery(sqltran, CommandType.Text, str.ToString());

                    //StringBuilder strdetail = new StringBuilder();
                    str.Clear();
                    str.Append("delete from Con_Instock_Detailed where Group_ID = " + OrdNo);
                    rowcount = rowcount + db.ExecuteNonQuery(sqltran, CommandType.Text, str.ToString());
                    sqltran.Commit();
                    sqlconn.Close();
                }
                catch (Exception ex)
                {
                    sqltran.Rollback();
                    sqlconn.Close();
                    throw new Exception(ex.Message);
                }
            };
            return rowcount;
        }

        /// <summary>
        /// 获取食材主表的数据
        /// </summary>
        /// <returns></returns>
        public int GetRowCount()
        {
            string query = "select count(1) from Con_Instock_Group";
            sqldb db = new sqldb();
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(rowcount);
        }
    }
}
